USE <database_name>;
GO

SET NOCOUNT ON;
GO

PRINT 'implicit conversions in the plan cache';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 

DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT 
   stmt.value(N'(@StatementText)[1]', N'NVARCHAR(MAX)') AS statement_text, 
   t.value(N'(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', N'VARCHAR(128)') AS [schema_name], 
   t.value(N'(ScalarOperator/Identifier/ColumnReference/@Table)[1]', N'VARCHAR(128)') AS table_name, 
   t.value(N'(ScalarOperator/Identifier/ColumnReference/@Column)[1]', N'VARCHAR(128)') AS column_name, 
   ic.DATA_TYPE AS convert_from, 
   ic.CHARACTER_MAXIMUM_LENGTH AS convert_from_length, 
   t.value(N'(@DataType)[1]', N'VARCHAR(128)') AS convert_to, 
   t.value(N'(@Length)[1]', N'INT') AS convert_to_length, 
   query_plan 
FROM sys.dm_exec_cached_plans AS cp
	CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
	CROSS APPLY query_plan.nodes(
		'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
	CROSS APPLY stmt.nodes(
		'.//Convert[@Implicit="1"]') AS n(t) 
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME (ic.TABLE_SCHEMA) 
		= t.value(N'(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', N'VARCHAR(128)')
   AND QUOTENAME (ic.TABLE_NAME) 
		= t.value(N'(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'NVARCHAR(128)')
   AND ic.COLUMN_NAME 
		= t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'NVARCHAR(128)') 
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]')	= 1 
OPTION (MAXDOP 1);
GO

PRINT 'probe residuals in the plan cache';
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    
DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());

WITH XMLNAMESPACES 
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT
	query_plan,
    build_schema,
    build_table,
    build_column,
    ic.DATA_TYPE AS build_column_type, 
    ISNULL (CAST (ic.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 
	        (CAST (ic.NUMERIC_PRECISION AS NVARCHAR)
            + N',' + CAST (ic.NUMERIC_SCALE AS NVARCHAR))) 
			AS build_column_length,
   probe_schema,
   probe_table,
   probe_column,
   ic2.DATA_TYPE AS probe_column_type, 
   ISNULL (CAST (ic2.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 
		    (CAST (ic2.NUMERIC_PRECISION AS NVARCHAR)
            + N',' + CAST (ic2.NUMERIC_SCALE AS NVARCHAR))) 
			AS probe_column_length
FROM
(
	SELECT 
	   query_plan,
	   t.value(N'(../HashKeysBuild/ColumnReference/@Schema)[1]', N'NVARCHAR(128)') AS build_schema,
	   t.value(N'(../HashKeysBuild/ColumnReference/@Table)[1]', N'NVARCHAR(128)') AS Build_table,
	   t.value(N'(../HashKeysBuild/ColumnReference/@Column)[1]', N'NVARCHAR(128)') AS build_column,
	   t.value(N'(../HashKeysProbe/ColumnReference/@Schema)[1]', N'NVARCHAR(128)') AS probe_schema,
	   t.value(N'(../HashKeysProbe/ColumnReference/@Table)[1]', N'NVARCHAR(128)') AS probe_table,
	   t.value(N'(../HashKeysProbe/ColumnReference/@Column)[1]', N'NVARCHAR(128)') AS probe_column
	FROM sys.dm_exec_cached_plans AS cp 
		CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
		CROSS APPLY query_plan.nodes(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) 
		CROSS APPLY stmt.nodes(
			N'.//Hash/ProbeResidual') AS n(t) 
	WHERE t.exist(N'../HashKeysProbe/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 
	) AS Probes
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS ic
   ON QUOTENAME (ic.TABLE_SCHEMA) 
		= Probes.build_schema
   AND QUOTENAME (ic.TABLE_NAME) 
		= Probes.build_table
   AND ic.COLUMN_NAME 
		= Probes.build_column
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS ic2
   ON QUOTENAME (ic2.TABLE_SCHEMA) = Probes.probe_schema
   AND QUOTENAME (ic2.TABLE_NAME) = Probes.probe_table
   AND ic2.COLUMN_NAME = Probes.probe_column
WHERE ic.DATA_TYPE <> ic2.DATA_TYPE
    OR (
     ic.DATA_TYPE = ic2.DATA_TYPE 
     AND ISNULL (CAST (ic.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 
                (CAST (ic.NUMERIC_PRECISION AS NVARCHAR)
                + N',' + CAST (ic.NUMERIC_SCALE AS NVARCHAR))) 
     <> ISNULL (CAST (ic2.CHARACTER_MAXIMUM_LENGTH AS NVARCHAR), 
                (CAST (ic2.NUMERIC_PRECISION AS NVARCHAR)
                + N',' + CAST (ic2.NUMERIC_SCALE AS NVARCHAR)))
        )
OPTION (MAXDOP 1);